package com.fellan.persistance;

import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import com.fellan.R;
import com.fellan.model.Line;
import com.fellan.model.Station;
import com.fellan.utils.DataUtils;

import java.io.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Hashtable;

/**
 * Created by IntelliJ IDEA.
 * User: Edy
 * Date: 26.05.2012
 * Time: 14:48
 * To change this template use File | Settings | File Templates.
 */
public class DBManager {
    /**
     * The unique, private instance of this singleton
     */
    private static DBManager instance;
    public static ArrayList<Line> linesList = null;
    public static ArrayList<Station> stationsList = null;
    public static ArrayList<Line> linesForStationList = null;
    public static ArrayList<Station> stationsForLineList = null;
    public static ArrayList<Date>  listDate=null;
    public static boolean FingReading = false;
    public Hashtable<Station,ArrayList<Line>>  hashtable=null;
    /**
     * SqlLite database instance
     */
    private SQLiteDatabase myDataBase = null;

    /**
     * Method used to get instance of this manager
     *
     * @return Instance of DBManager
     */
    public static DBManager getInstance() {
        if (instance == null)
            instance = new DBManager();
        return instance;
    }

    private DBManager() {
        // If the database is only stored in the application resources
        copyDatabase();
    }

    /**
     * Method that copies the database.sqlite from the bundle to the device in case it does not exist
     */
    private void copyDatabase() {
        //Create a data stream to read a raw resource
        InputStream inputStream = DataUtils.getApplicationContext().getResources().openRawResource(R.raw.mydatabase);

        //Create a directory that will hold the database
        File directory = new File(DataUtils.getApplicationContext().getFilesDir().getParent(), PersistenceConstants.DATABASES_FOLDER_NAME);

        if (!directory.exists()) {
            directory.mkdir();
        }

        //Create database file
        File databaseFile = new File(directory, PersistenceConstants.DB_NAME);

        // Indicates if the app_database.sqlite file exists in the application sandbox (on the device)
        boolean databaseExists = databaseFile.exists();

        // If the app_database.sqlite does not exist copy it from the app bundle.
        if (!databaseExists) {
            try {
                OutputStream outputStream;

                //Create a new empty file
                databaseFile.createNewFile();

                //Constructs a new output stream that writes to file.
                outputStream = new FileOutputStream(databaseFile);

                //Get a bytes buffer
                byte[] buf = new byte[1024];

                //Length of the buffer that is used to read from input stream
                int length;
                while ((length = inputStream.read(buf)) > 0) {
                    outputStream.write(buf, 0, length);
                }

                //Close the streams
                inputStream.close();
                outputStream.close();
            } catch (FileNotFoundException fileNotFoundException) {
                fileNotFoundException.printStackTrace();
            } catch (IOException IOException) {
                IOException.printStackTrace();
            }
        }
    }

    /**
     * Method used to open database
     */
    private void openDatabase() {
        String myPath = DataUtils.getApplicationContext().getFilesDir().getParent() + "/" + PersistenceConstants.DATABASES_FOLDER_NAME + "/" + PersistenceConstants.DB_NAME;

        if (myDataBase == null) {
            myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
        } else if (!myDataBase.isOpen()) {
            myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
        }

        FingReading = true;
    }

    /**
     * Method used to get all lines
     */
    public void getAllLines()
    {
        Thread backgroundThread = new Thread(new Runnable() {
            @Override
            public void run() {
                //Open database
                openDatabase();


                //Query database for values
                Cursor cursor = myDataBase.query(PersistenceConstants.LINII_TABEL,
                        new String[]{PersistenceConstants.LINII_ID,PersistenceConstants.LINII_NUME},
                        null, null, null, null, null);

                //Construct the list to be passed to business level
                ArrayList<Line> linesArray = new ArrayList<Line>();

                //Loop through all the values from cursor and add them to the array list
                while (cursor.moveToNext()) {
                    Line line = new Line(cursor.getString(1),cursor.getInt(0));
                    linesArray.add(line);
                }

                //Close cursor
                cursor.close();

                //Close database
               // myDataBase.close();
                //Create a new Intent
                Intent resultIntent = new Intent(PersistenceConstants.PC_ALL_LINES_RECEIVED_SUCCESSFULLY);

                linesList = linesArray;

                //Broadcast intent
                DataUtils.getApplicationContext().sendBroadcast(resultIntent);
            }
        });
        backgroundThread.start();
    }

    public void getAllStations()
    {

        Thread backgroundThread = new Thread(new Runnable() {
            public void run() {
                openDatabase();
                String[] columns={   PersistenceConstants.STATII_ID,PersistenceConstants.STATII_NUME,PersistenceConstants.STATII_LAT,PersistenceConstants.STATII_LNG};
                Cursor cursor=myDataBase.query(PersistenceConstants.STATII_TABEL,columns,null,null,null,null,PersistenceConstants.STATII_NUME);
                ArrayList<Station> listStations=new ArrayList<Station>();
                while(cursor.moveToNext())
                {
                    Station station =new Station(cursor.getInt(cursor.getColumnIndex(PersistenceConstants.STATII_ID)),
                            cursor.getString(cursor.getColumnIndex(PersistenceConstants.STATII_NUME)),
                            cursor.getFloat(cursor.getColumnIndex(PersistenceConstants.STATII_LAT)),
                            cursor.getFloat(cursor.getColumnIndex(PersistenceConstants.STATII_LNG)));
                    listStations.add(station);

                }
                cursor.close();
                //myDataBase.close();
                Intent resultIntent = new Intent(PersistenceConstants.PC_ALL_STATIONS_RECEIVED_SUCCESSFULLY);

                stationsList = listStations;

                //Broadcast intent
                DataUtils.getApplicationContext().sendBroadcast(resultIntent);
            }
        }) ;
        backgroundThread.start();
    }



    public void getStationsForLine(int line)
    {
        final int lineF = line;
        Thread backgroundThread = new Thread(new Runnable() {
            public void run() {
                openDatabase();
                Cursor cursor=myDataBase.rawQuery("Select "+ PersistenceConstants.STATII_TABEL + "."
                        + PersistenceConstants.STATII_ID+" , "+PersistenceConstants.STATII_TABEL + "."
                        + PersistenceConstants.STATII_NUME +" from "+
                        PersistenceConstants.STATIELINIE_TABEL+" inner join "+PersistenceConstants.STATII_TABEL+" on "+
                        PersistenceConstants.STATIELINIE_TABEL+ "." + PersistenceConstants.STATIELINIE_IDSTATIE
                        + " = "+PersistenceConstants.STATII_TABEL + "." + PersistenceConstants.STATII_ID + " where "
                        + PersistenceConstants.STATIELINIE_TABEL + "." + PersistenceConstants.STATIELINIE_IDLINIE+"=? order by "
                        + PersistenceConstants.STATIELINE_DISTANTA,new String[]{String.valueOf(lineF)});
                ArrayList<Station> listStations=new ArrayList<Station>();
                while(cursor.moveToNext())
                {
                    listStations.add(new Station(cursor.getInt(0),cursor.getString(1)));

                }
                cursor.close();

                //myDataBase.close();
                Intent resultIntent = new Intent(PersistenceConstants.PC_ALL_STATIONSBYLINE_RECEIVED_SUCCESSFULLY);

                stationsForLineList = listStations;

                //Broadcast intent
                DataUtils.getApplicationContext().sendBroadcast(resultIntent);


            }
        }) ;
        backgroundThread.start();
    }

    public void getLinesForStation(int station)
    {
        final int stationF=station;
        Thread backgroundThread = new Thread(new Runnable() {
            public void run() {
                openDatabase();
                String[] columns={PersistenceConstants.LINII_ID,PersistenceConstants.STATIELINIE_IDLINIE,
                        PersistenceConstants.STATIELINIE_IDSTATIE};
                Cursor cursor = null;
                try
                {
                    cursor=myDataBase.rawQuery("Select "+ PersistenceConstants.LINII_TABEL + "."
                            + PersistenceConstants.LINII_ID+" , "+PersistenceConstants.LINII_TABEL + "."
                            + PersistenceConstants.LINII_NUME +" from "+
                            PersistenceConstants.STATIELINIE_TABEL+" inner join "+PersistenceConstants.LINII_TABEL+" on "+
                            PersistenceConstants.STATIELINIE_TABEL+ "." + PersistenceConstants.STATIELINIE_IDLINIE
                            + " = "+PersistenceConstants.LINII_TABEL + "." + PersistenceConstants.LINII_ID + " where "
                            + PersistenceConstants.STATIELINIE_TABEL + "." + PersistenceConstants.STATIELINIE_IDSTATIE+"=? order by "
                            + PersistenceConstants.LINII_TABEL+"."+PersistenceConstants.LINII_NUME,new String[]{String.valueOf(stationF)});
                }
                catch (Exception e)
                {
                    Log.e("RATUC",e.getMessage());
                    e.printStackTrace();
                }
                ArrayList<Line> listLines=new ArrayList<Line>();
                while(cursor.moveToNext())
                {
                    //int id=cursor.getInt(cursor.getColumnIndex(PersistenceConstants.STATIELINIE_IDSTATIE));
                    listLines.add(new Line(cursor.getString(1),cursor.getInt(0)));

                }
                cursor.close();
                //myDataBase.close();
                Intent resultIntent = new Intent(PersistenceConstants.PC_ALL_LINESBYSTATION_RECEIVED_SUCCESSFULLY);

                linesForStationList = listLines;

                //Broadcast intent
                DataUtils.getApplicationContext().sendBroadcast(resultIntent);
            }
        }) ;
        backgroundThread.start();
    }

    public Station getStationByID(int id)
    {
        String[] columns={   PersistenceConstants.STATII_ID,PersistenceConstants.STATII_NUME,PersistenceConstants.STATII_LAT,PersistenceConstants.STATII_LNG};
        openDatabase();
        Cursor cursor=myDataBase.query(PersistenceConstants.STATII_TABEL,columns,PersistenceConstants.STATII_ID+"="+id,null,null,null,null);
        cursor.moveToFirst();
         Station station =new Station(cursor.getInt(cursor.getColumnIndex(PersistenceConstants.STATII_ID)),
                    cursor.getString(cursor.getColumnIndex(PersistenceConstants.STATII_NUME)),
                    cursor.getFloat(cursor.getColumnIndex(PersistenceConstants.STATII_LAT)),
                    cursor.getFloat(cursor.getColumnIndex(PersistenceConstants.STATII_LNG)));
        cursor.close();
        return station;
    }

    public int detDistance(int idSLine,int idStation)
    {

        final int idStationF=idStation;
        final int idLineF=idSLine;
        Cursor cursor=myDataBase.rawQuery("Select "+ PersistenceConstants.STATIELINIE_TABEL + "."
                + PersistenceConstants.STATIELINE_DISTANTA+" from "+ PersistenceConstants.STATIELINIE_TABEL +" where " +
                PersistenceConstants.STATIELINIE_TABEL+"."+PersistenceConstants.STATIELINIE_IDLINIE+"='"+idLineF+ "' and "
                +PersistenceConstants.STATIELINIE_TABEL+"."+PersistenceConstants.STATIELINIE_IDSTATIE+"='"+idStationF+"'"
                ,null);
        cursor.moveToFirst();
        int value=cursor.getInt(0);
        cursor.close();
        return value;

    }

    public void getSchedule(int line, int station, DataUtils.Zile ziua)
    {
        String table = null;
        switch (ziua)
        {
            case ZILE_LUCRATOARE:
                table=PersistenceConstants.ORARLV_TABEL;
                break;
            case SAMBATA:
                table=PersistenceConstants.ORARS_TABEL;
                break;
            case DUMINICA:
                table=PersistenceConstants.ORARD_TABEL;
                break;
        }
        final int idStationF=station;
        final int idLineF=line;
        final String tableF=table;

        //'select strftime(\'%H:%M\',OrarLV.OraPlecare) from OrarLV where OrarLV.idLinie=2'

        Thread backgroundThread = new Thread(new Runnable() {
            @Override
            public void run() {
                //Open database
                openDatabase();
                int dist=detDistance(idLineF,idStationF);
                Cursor cursor=myDataBase.rawQuery("Select strftime('%H:%M'," + PersistenceConstants.ORAR_ORA_PLECARE + ") from " + tableF + " where " + PersistenceConstants.ORAR_ID + "=" + idLineF, null);
                SimpleDateFormat dateFormat = new SimpleDateFormat("HH:mm");

                Date convertedDate,date2;
                listDate = new ArrayList<Date>();

                while(cursor.moveToNext())
                {
                    String dateString=cursor.getString(0);
                    try {
                        convertedDate = dateFormat.parse(dateString);
                        date2 = new Date(convertedDate.getTime() + dist * 60 * 1000);
                        listDate.add(date2);
                    } catch (ParseException e) {
                        e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
                    }

                }
                cursor.close();
                //myDataBase.close();
                Intent resultIntent = new Intent(PersistenceConstants.PC_SCHEDULE_RECEIVED_SUCCESSFULLY);

                //Broadcast intent
                DataUtils.getApplicationContext().sendBroadcast(resultIntent);
            }
        });
        backgroundThread.start();
    }

    public void getAllLinesForAllStations()
    {
        Thread backgroundThread = new Thread(new Runnable() {
            @Override
            public void run() {
                openDatabase();
                String[] columns={   PersistenceConstants.STATII_ID,PersistenceConstants.STATII_NUME,PersistenceConstants.STATII_LAT,PersistenceConstants.STATII_LNG};
                Cursor cursor=myDataBase.query(PersistenceConstants.STATII_TABEL,columns,null,null,null,null,PersistenceConstants.STATII_NUME);
                //ArrayList<Station> listStations=new ArrayList<Station>();
                hashtable = new Hashtable<Station, ArrayList<Line>>();
                while(cursor.moveToNext())
                {
                    Station station =new Station(cursor.getInt(cursor.getColumnIndex(PersistenceConstants.STATII_ID)),
                            cursor.getString(cursor.getColumnIndex(PersistenceConstants.STATII_NUME)),
                            cursor.getFloat(cursor.getColumnIndex(PersistenceConstants.STATII_LAT)),
                            cursor.getFloat(cursor.getColumnIndex(PersistenceConstants.STATII_LNG)));

                    // cauta liniile pt statie
                    Cursor cursor2 = null;
                    try
                    {
                        cursor2=myDataBase.rawQuery("Select "+ PersistenceConstants.LINII_TABEL + "."
                                + PersistenceConstants.LINII_ID+" , "+PersistenceConstants.LINII_TABEL + "."
                                + PersistenceConstants.LINII_NUME +" from "+
                                PersistenceConstants.STATIELINIE_TABEL+" inner join "+PersistenceConstants.LINII_TABEL+" on "+
                                PersistenceConstants.STATIELINIE_TABEL+ "." + PersistenceConstants.STATIELINIE_IDLINIE
                                + " = "+PersistenceConstants.LINII_TABEL + "." + PersistenceConstants.LINII_ID + " where "
                                + PersistenceConstants.STATIELINIE_TABEL + "." + PersistenceConstants.STATIELINIE_IDSTATIE+"=? order by "
                                + PersistenceConstants.LINII_TABEL+"."+PersistenceConstants.LINII_NUME,new String[]{String.valueOf(station.getIdStation())});
                    }
                    catch (Exception e)
                    {
                        e.printStackTrace();
                    }
                    ArrayList<Line> listLines=new ArrayList<Line>();
                    while(cursor2.moveToNext())
                    {
                        //int id=cursor.getInt(cursor.getColumnIndex(PersistenceConstants.STATIELINIE_IDSTATIE));
                        listLines.add(new Line(cursor2.getString(1),cursor2.getInt(0)));
                    }
                    cursor2.close();
                    hashtable.put(station,listLines);
                }
                cursor.close();
                //myDataBase.close();
                Intent resultIntent = new Intent(PersistenceConstants.PC_ALL_STATIONS_WITH_LINES_RECEIVED_SUCCESSFULLY);

                //Broadcast intent
                DataUtils.getApplicationContext().sendBroadcast(resultIntent);
            }
        });
        backgroundThread.start();
    }
}
